﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Web;

namespace MyCompanyName.AbpZeroTemplate
{
    public class ExcelHelper
    {
        public static System.Data.DataTable ReadExcel(String strFileName)
        {
            Workbook book = new Workbook(strFileName);
            Worksheet sheet = book.Worksheets[0];
            Cells cells = sheet.Cells;
            return cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);
        }


        /// <summary>
        /// 简单的二维表格数据导入，第一行为标题行，第二行起为数据行，无其它多余数据。且标题行与实体属性名相等。
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="FileName"></param>
        /// <param name="InputStream"></param>
        /// <returns></returns>
        public static List<T> SimpleImport<T>(string FileName, System.IO.Stream InputStream, int sheetIndex = 0, int rowColumnIndex = 0, int rowContentIndex = 1) where T : class,new()
        {
            System.Data.DataTable dt = import(FileName, InputStream, sheetIndex, rowColumnIndex, rowContentIndex);
            List<T> res = toEntities<T>(dt);
            return res;
        }


        private static System.Data.DataTable import(string FileName, System.IO.Stream InputStream, int sheetIndex = 0, int rowColumnIndex = 0, int rowContentIndex = 1)
        {
            //var file = RequestContext.Files[0];
            //file.FileName
            //file.InputStream

            LoadOptions lo = new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx);
            if (FileName.ToLower().EndsWith(".xls"))
            {
                lo = new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Excel97To2003);
            }
            var book = new Aspose.Cells.Workbook(InputStream, lo);
            var cells = book.Worksheets[sheetIndex].Cells;
            int rowCount = cells.MaxDataRow;
            int columnCount = cells.MaxDataColumn;


            System.Data.DataTable dt = new System.Data.DataTable(FileName);
            Dictionary<int, string> cols = new Dictionary<int, string>(columnCount);

            for (int i = 0; i <= columnCount; i++)
            {
                string val = cells[rowColumnIndex, i].StringValue.Trim();
                if (string.IsNullOrEmpty(val))
                {
                    continue;
                }
                if (cols.Values.Any(x => x == val))
                {
                    throw new Exception("列【" + val + "】有重复");
                }
                cols.Add(i, val);
                dt.Columns.Add(val);
            }

            for (int i = rowContentIndex; i <= rowCount; i++)
            {
                DataRow dr = dt.NewRow();
                foreach (var kv in cols)
                {
                    string val = cells[i, kv.Key].StringValue.Trim();
                    dr[kv.Value] = val;
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        /// <summary>
        /// DataTable to Entities
        /// </summary>
        /// <typeparam name="T">Entity</typeparam>
        /// <param name="dt">DataTable</param>
        /// <returns>List<T</returns>
        private static List<T> toEntities<T>(DataTable dt) where T : class,new()
        {
            //IsNullable
            if (null == dt || dt.Rows.Count == 0)
                return null;

            //Initial Entities
            List<T> entities = new List<T>();
            try
            {
                PropertyInfo[] pArray = typeof(T).GetProperties();
                foreach (DataRow row in dt.Rows)
                {
                    T entity = new T();
                    Array.ForEach<PropertyInfo>(pArray, p =>
                    {
                        if (dt.Columns.Contains(p.Name) && row[p.Name] != null)
                            p.SetValue(entity, row[p.Name], null);
                    });
                    entities.Add(entity);
                }
                return entities;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        public static List<dynamic> ToDynamic(List<Dictionary<string, string>> excelData)
        {
            List<dynamic> lst = new List<dynamic>();
            foreach (var row in excelData)
            {
                dynamic dyn = new System.Dynamic.ExpandoObject();
                foreach (var kv in row)
                {
                    ((IDictionary<string, object>)dyn).Add(kv.Key, kv.Value);
                }
                lst.Add(dyn);
            }
            return lst;
        }


        public static void ResponseFile(string fileName, string filePath)
        {
            System.Web.HttpContext.Current.Response.Clear();
            System.Web.HttpContext.Current.Response.ClearHeaders();
            System.Web.HttpContext.Current.Response.Buffer = true;
            System.Web.HttpContext.Current.Response.Charset = "utf-8";
            System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition",
                "Attachment;FileName=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8));
            System.Web.HttpContext.Current.Response.ContentEncoding = Encoding.UTF8;
            string outputPath = System.Web.HttpContext.Current.Request.PhysicalApplicationPath + filePath;
            //以字符流的形式下载文件
            FileStream fs = new FileStream(outputPath, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            System.Web.HttpContext.Current.Response.BinaryWrite(bytes);
            System.Web.HttpContext.Current.Response.Flush();
            System.Web.HttpContext.Current.Response.End();
        }

    }
}
